Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶

Angaben Studierende(r) (fehlende Angaben ergänzen)¶

Vorname: Micola
Nachname: Vital
Immatrikulationsnummer: 20267217
Modul: Data Science
Prüfungsdatum / Raum / Zeit: 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45
Erlaubte Hilfsmittel: w.MA.XX.DS.24HS (Data Science)
Open Book, Eigener Computer, Internet-Zugang
Nicht erlaubt: Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT)
sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen.

Bewertungskriterien¶

(max. erreichbare Punkte: 48)¶

Kategorie Beschreibung Punkteverteilung
Code nicht lauffähig oder Ergebnisse nicht sinnvoll Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. 0 Punkte
Code lauffähig, aber mit gravierenden Mängeln Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. 25% der max. erreichbaren Punkte
Code lauffähig, aber mit mittleren Mängeln Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. 50% der max. erreichbaren Punkte
Code lauffähig, aber mit minimalen Mängeln Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. 75% der max. erreichbaren Punkte
Code lauffähig und korrekt Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. 100% der max. erreichbaren Punkte

Python Libraries und Settings¶

In [139]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text

# Ignore warnings
import warnings
warnings.filterwarnings("ignore")

print(os.getcwd())

/workspaces/python_postgresql_postgis

Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶

1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶

GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶

Hinweis:

  • Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
  • Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.

2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶

In [140]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"

# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
                    "@" + host + ":" + port + "/" + database

# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)

# Test der Connection
with engine.connect() as connection:
    result = connection.execute(text('SELECT current_database()'))
    print(result.fetchone())

# Verbindung trennen
engine.dispose()

('osm_switzerland',)

Aufgaben (Dieser Teil wird bewertet!)¶

Hinweise zu den folgenden Aufgabenstellungen:

  • In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
    1. Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
    2. Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
  • In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
  • In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Beachten Sie, dass für die Punktevergabe auch die weiteren Anforderungen zu den Fragen unter 'Details zur Aufgabenstellung' herangezogen werden.

Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
  • Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
  • Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [141]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'car_repair';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[141]:
osm_id shop geom
0 1811755810 car_repair POINT (9.62898 47.45412)
1 9408250312 car_repair POINT (9.63098 47.45327)
2 5254765356 car_repair POINT (9.43594 47.50471)
3 9408188280 car_repair POINT (9.46826 47.49408)
4 3346119599 car_repair POINT (9.49096 47.47737)
... ... ... ...
1459 4171944125 car_repair POINT (9.16975 47.61128)
1460 4171944126 car_repair POINT (9.1698 47.60761)
1461 7077905514 car_repair POINT (9.2903 47.6054)
1462 7077905513 car_repair POINT (9.28444 47.60599)
1463 1492440172 car_repair POINT (9.20147 47.63996)

1464 rows × 3 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [142]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[142]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [143]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.amenity,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.amenity = 'biergarten';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[143]:
osm_id amenity name geom
0 704467869 biergarten Bierhalle POINT (9.6068 47.40694)
1 370319479 biergarten Center da Surf POINT (9.79135 46.45518)
2 2725618744 biergarten La Bulle POINT (7.10772 45.92965)
3 648260667 biergarten None POINT (7.38983 46.12865)
4 2991096399 biergarten Stierentungel-Beizli POINT (7.37019 46.39482)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [144]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[144]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
  • Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
  • Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
  • Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
  • Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.

(max. erreichbare Punkte: 6)

In [145]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
    p.osm_id,
    p."addr:street",
    p."addr:housenumber",
    p."addr:city",
    p."addr:postcode",
    p.building,
    st_transform(p.way, 4326) AS geom
FROM
    public.planet_osm_polygon AS p
WHERE 
    p."addr:street" IS NOT NULL
    AND p."addr:housenumber" IS NOT NULL
	AND p."addr:city" IS NOT NULL
	AND p."addr:postcode" IS NOT NULL
    AND p."addr:city" = 'Winterthur'
    AND p."addr:postcode" IN ('8400')
    AND p."addr:street" = 'Stadthausstrasse';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[145]:
osm_id addr:street addr:housenumber addr:city addr:postcode building geom
0 134980581 Stadthausstrasse 10b Winterthur 8400 yes POLYGON ((8.72652 47.50075, 8.72661 47.50063, ...
1 9264543 Stadthausstrasse 4a Winterthur 8400 government POLYGON ((8.73111 47.50115, 8.73112 47.50085, ...
2 22301937 Stadthausstrasse 4 Winterthur 8400 apartments POLYGON ((8.73232 47.50109, 8.73233 47.50107, ...
3 75027485 Stadthausstrasse 31 Winterthur 8400 office POLYGON ((8.73162 47.50041, 8.73165 47.50029, ...
4 75027472 Stadthausstrasse 35 Winterthur 8400 apartments POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8...
... ... ... ... ... ... ... ...
56 188158695 Stadthausstrasse 113 Winterthur 8400 apartments POLYGON ((8.72702 47.4999, 8.72706 47.49975, 8...
57 94283231 Stadthausstrasse 145 Winterthur 8400 apartments POLYGON ((8.72414 47.4995, 8.72417 47.49939, 8...
58 94283304 Stadthausstrasse 143 Winterthur 8400 office POLYGON ((8.72427 47.49952, 8.72431 47.4994, 8...
59 24804763 Stadthausstrasse 22 Winterthur 8400 office POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8...
60 26992511 Stadthausstrasse 24 Winterthur 8400 retail POLYGON ((8.72405 47.49972, 8.72407 47.49967, ...

61 rows × 7 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [146]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=17, 
               tiles='ESRIWorldImagery')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[146]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [147]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.highway,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_roads h
        WHERE h.highway = 'motorway';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[147]:
osm_id highway geom
0 1236416666 motorway LINESTRING (9.64218 47.43433, 9.6422 47.43402,...
1 552469430 motorway LINESTRING (9.64232 47.43412, 9.6423 47.43469)
2 552469432 motorway LINESTRING (9.6423 47.43469, 9.64232 47.43504,...
3 552469428 motorway LINESTRING (9.64312 47.43793, 9.6429 47.43753,...
4 186132194 motorway LINESTRING (9.64235 47.43533, 9.6424 47.43568,...
... ... ... ...
8263 137477250 motorway LINESTRING (9.15877 47.65537, 9.15878 47.65526...
8264 100501714 motorway LINESTRING (9.15428 47.65126, 9.15442 47.65142...
8265 100501659 motorway LINESTRING (9.15415 47.6513, 9.15396 47.65108,...
8266 137477248 motorway LINESTRING (9.15384 47.65071, 9.15395 47.65087...
8267 100501651 motorway LINESTRING (9.15834 47.65468, 9.15789 47.65424...

8268 rows × 3 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [148]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=9, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    line_weight=3,
    line_color='red'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[148]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features
  • Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.

(max. erreichbare Punkte: 8)

In [149]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT 
            p.osm_id,
            p.waterway,
            1 as group_id,
            ST_TRANSFORM(ST_UNION(ST_Buffer(p.way::geometry, 2000)), 4326) AS geom
        FROM public.planet_osm_line AS p
        WHERE
            waterway = 'river'
		group by p.osm_id, p.waterway;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[149]:
osm_id waterway group_id geom
0 4223448 river 1 POLYGON ((7.61135 46.75204, 7.61112 46.75215, ...
1 4224616 river 1 POLYGON ((7.62089 46.74728, 7.62027 46.74748, ...
2 4245442 river 1 POLYGON ((8.61599 46.8591, 8.61509 46.86111, 8...
3 4245448 river 1 POLYGON ((8.62089 46.82734, 8.62063 46.82792, ...
4 4254242 river 1 POLYGON ((8.53617 47.55367, 8.53615 47.55396, ...
... ... ... ... ...
2552 1317015786 river 1 POLYGON ((8.74907 46.50162, 8.75252 46.50114, ...
2553 1317015787 river 1 POLYGON ((8.73866 46.50069, 8.73864 46.50073, ...
2554 1318731230 river 1 POLYGON ((6.00715 46.18904, 6.00742 46.1891, 6...
2555 1319653743 river 1 POLYGON ((10.11183 46.68295, 10.11115 46.68303...
2556 1319653744 river 1 POLYGON ((10.10057 46.68987, 10.09988 46.69099...

2557 rows × 4 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [150]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[150]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
  • Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
  • Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [151]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            h."addr:city",
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'bakery'
        AND h."addr:city" IN ('Zürich', 'Winterthur')
        ORDER BY h.osm_id ASC;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[151]:
osm_id shop name addr:city geom
0 252457457 bakery Steiner Flughafebeck Zürich POINT (8.49779 47.40311)
1 266630770 bakery Brezelkönig Zürich POINT (8.48875 47.39147)
2 267879346 bakery Walter Buchmann Zürich POINT (8.51892 47.36239)
3 268602152 bakery Moser's Zürich POINT (8.54945 47.3632)
4 270794699 bakery John Baker Zürich POINT (8.56645 47.36493)
... ... ... ... ... ...
101 10082330824 bakery RAM3 Winterthur POINT (8.74193 47.49395)
102 10884168277 bakery Juliette Zürich POINT (8.5328 47.36658)
103 10946043353 bakery Babu's Bakery Zürich POINT (8.51568 47.37473)
104 10955852823 bakery Täglich Brot Zürich POINT (8.50705 47.36079)
105 11951988149 bakery Wagner Zürich POINT (8.51763 47.3698)

106 rows × 5 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [152]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=12, 
               tiles='EsriWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[152]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶

Details zur Aufgabenstellung:

  • Sie finden die Daten in der Tabelle 'planet_osm_point'.
  • Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
  • Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [153]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            p.osm_id,
            p.shop,
            p.name,
            ST_Distance(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography 
            ) AS distance_meters,
            ST_TRANSFORM(p.way, 4326) AS geom
        FROM
            planet_osm_point AS p
        WHERE
            p.shop = 'hairdresser'
            AND ST_DWithin(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography, 
                500
            );"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[153]:
osm_id shop name distance_meters geom
0 11758825735 hairdresser MSH Salon 361.728635 POINT (8.5424 47.38062)
1 1357158512 hairdresser Coiffeur-Studio Silvia Baumgartner 310.980273 POINT (8.54214 47.38017)
2 4833061593 hairdresser McCoiffure 226.319141 POINT (8.53695 47.37931)
3 4424939218 hairdresser McCoiffure 111.635316 POINT (8.53791 47.37791)
4 4244059289 hairdresser Art Coiffure Kaiser 133.439602 POINT (8.53788 47.37745)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [154]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=16, 
               tiles='EsriWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
    popup=folium.GeoJsonPopup(fields=['name']) # popups anpassen gemäss Spalten, welche zuvor ausgegeben werden
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[154]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶

In [155]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime

print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')

-----------------------------------
POSIX
Linux | 6.5.0-1025-azure
Datetime: 2024-10-07 09:29:13
Python Version: 3.12.1
IP Address: 127.0.0.1
-----------------------------------